/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Helper.AccountHelper;
import Helper.DepartmentHelper;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author KIENLT
 */
public class AccountDAO {
    
    public static ResultSet checkLogin(Connection cnn, AccountHelper obj) throws Exception {
        String strSQL = "SELECT * FROM tblAccount WHERE AccountName=? and AccountPass=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getUserName());
        stmt.setString(2, obj.getPassword());
        return stmt.executeQuery();
    }
    
    public static boolean checkUserName(Connection cnn, AccountHelper obj) throws Exception {
        boolean check = false;
        String strSQL = "{call checkUserAccount(?)}";
        CallableStatement stmt = cnn.prepareCall(strSQL);
        stmt.setString(1, obj.getUserName());
        ResultSet rs = stmt.executeQuery();
        if(rs.next()){
            check = true;
        }
        return check;
    }
    
    public static int Insert(Connection cnn, AccountHelper obj) throws Exception{
        String strSQL = "{call insertAccount(?,?,?,?,?,?,?,?,?,?)}";
        CallableStatement stmt = cnn.prepareCall(strSQL);
        stmt.setString(1, obj.getFullName());
        stmt.setString(2, obj.getUserName());
        stmt.setString(3, obj.getPassword());
        stmt.setString(4, obj.getDatetime());
        stmt.setString(5, obj.getAddress());
        stmt.setString(6, obj.getPhone());
        stmt.setString(7, obj.getGender());
        stmt.setString(8, obj.getEmail());
        stmt.setString(9, obj.getDepartmentId());
        stmt.setString(10, obj.getAccountLevelId());
        return stmt.executeUpdate();
    }
    
    public static int UpdateAccount(Connection cnn, AccountHelper obj) throws Exception {
        String strSQL = "{call updateAccount(?,?,?,?,?,?,?,?,?,?,?)}";
        CallableStatement stmt = cnn.prepareCall(strSQL);
        stmt.setString(1, obj.getUserName());
        stmt.setString(2, obj.getPassword());
        stmt.setString(3, obj.getFullName());
        stmt.setString(4, obj.getDatetime());
        stmt.setString(5, obj.getAddress());
        stmt.setString(6, obj.getPhone());
        stmt.setString(7, obj.getGender());
        stmt.setString(8, obj.getEmail());
        stmt.setString(9, obj.getDepartmentId());
        stmt.setString(10, obj.getAccountLevelId());
        stmt.setString(11, obj.getAccountId());
        return stmt.executeUpdate();
    }
    
    public static int Update(Connection cnn, AccountHelper obj) throws Exception{
        String strSQL = "{call updateAccount(?,?,?,?,?,?,?,?)}";
        CallableStatement stmt = cnn.prepareCall(strSQL);
        stmt.setString(1, obj.getUserName());
        stmt.setString(2, obj.getPassword());
        stmt.setString(3, obj.getFullName());
        stmt.setString(4, obj.getDatetime());
        stmt.setString(5, obj.getAddress());
        stmt.setString(6, obj.getPhone());
        stmt.setString(7, obj.getGender());
        stmt.setString(8, obj.getEmail());
        return stmt.executeUpdate();
    }
    
    public static int Delete(Connection cnn, AccountHelper obj) throws Exception{
        String strSQL = "{call deleteAccount(?)}";
        CallableStatement stmt = cnn.prepareCall(strSQL);
        stmt.setString(1, obj.getAccountId());
        return stmt.executeUpdate();
    }
    
    public static ResultSet FillOne(Connection cnn, AccountHelper obj) throws Exception {
        String strSQL = "{call fillOneAccount(?)}";
        CallableStatement stmt = cnn.prepareCall(strSQL);
        stmt.setString(1, obj.getAccountId());
        return stmt.executeQuery();
    }
   
    public static ArrayList<AccountHelper> FillAll(Connection cnn, DepartmentHelper obj) throws Exception{
        ArrayList<AccountHelper> arrAccountList = new ArrayList<AccountHelper>();
        AccountHelper accountData = null;
        String strSQL = "SELECT * FROM tblAccount as a, tblDepartment as d, tblAccountLevel as al where a.DepartmentId=d.DepartmentId and a.AccountLevelId=al.AccountLevelId and a.DepartmentId=? and a.AccountLevelId<>1";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getDepartmentID());
        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            accountData = new AccountHelper();
            accountData.setAccountId(rs.getString("AccountId"));
            accountData.setFullName(rs.getString("FullName"));
            accountData.setPhone(rs.getString("Phone"));
            accountData.setEmail(rs.getString("Email"));
            accountData.setAccountLevelId(rs.getString("AccountLevelName"));
            arrAccountList.add(accountData);
        }
        return arrAccountList;
    }
    
   
    public static ResultSet FillAll(Connection cnn, AccountHelper obj) throws Exception{
        ArrayList<AccountHelper> arrAccountList = new ArrayList<AccountHelper>();
        AccountHelper accountData = null;
        int levelId = Integer.parseInt(obj.getAccountLevelId()) + 1;
        String strSQL = "SELECT * FROM tblAccount as a, tblDepartment as d, tblAccountLevel as al where a.DepartmentId=d.DepartmentId and a.AccountLevelId=al.AccountLevelId and a.DepartmentId=? and a.AccountLevelId=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getDepartmentId());
        stmt.setInt(2, levelId);
        return stmt.executeQuery();
    }
    
    public static ResultSet FillAll(Connection cnn) throws Exception{
        String strSQL = "{call fillAllAccountList}";
        CallableStatement stmt = cnn.prepareCall(strSQL);
        return stmt.executeQuery();
    }
}
